# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import MySQLdb
import MySQLdb.cursors
from twisted.enterprise import adbapi
import xlsxwriter


class JobsspiderPipeline(object):
    def process_item(self, item, spider):
        return item


class MysqlPipeline(object):
    @classmethod
    def from_settings(cls, settings):
        config = dict(
            host=settings['MYSQL_HOST'],
            user=settings['MYSQL_USER'],
            passwd=settings['MYSQL_PASSWD'],
            db=settings['MYSQL_DBNAME'],
            use_unicode=True,
            charset=settings['MYSQL_CHARSET'],
            cursorclass=MySQLdb.cursors.DictCursor,
            port=settings['MYSQL_PORT']
        )
        dbpool = adbapi.ConnectionPool(
            'MySQLdb',
            **config
        )
        return cls(dbpool)

    def __init__(self, dbpool):
        self.dbpool = dbpool
        query = self.dbpool.runInteraction(self.create_table)
        query.addErrback(self.insert_err)

    def create_table(self, cursor):
        sql = 'create table if not exists jobs (id INT PRIMARY KEY auto_increment,job_name VARCHAR(255), job_company VARCHAR(255), job_money VARCHAR(255),job_place VARCHAR(255))'
        cursor.execute(sql)

    def process_item(self, item, spider):
        # 将Item存到数据库
        query = self.dbpool.runInteraction(self.insert_sql, item)
        # 当执行过程当中出现错误,会执行errback中的方法
        query.addErrback(self.insert_err)
        return item

    def insert_err(self, failed):
        print ">>>>>>>>>>>>>>>>>>>>", failed

    def insert_sql(self, cursor, item):
        sql = "insert into jobs (job_name, job_company, job_money, job_place) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql, (item['job_name'], item['job_company'], item['job_money'], item['job_place']))

    def __del__(self):
        pass


# class ExcelPipeline(object):
#     def __init__(self):
#         self.workbook = xlsxwriter.Workbook("jobs.xlsx")
#         self.worksheet = self.workbook.add_worksheet()
#         self.worksheet.write("A1", u"职位")
#         self.worksheet.write("B1", u"公司")
#         self.worksheet.write("C1", u"工资")
#         self.worksheet.write("D1", u"地点")
#
#     def process_item(self, item, spider):
#         money = item['job_name']
#         money = item['job_company']
#         money = item['job_money']
#         money = item['job_place']
#         return item
#
#     def __del__(self):
#
#         self.workbook.close()


class ExcelPipeline(object):
    def __init__(self):
        self.workbook = xlsxwriter.Workbook("jobs.xlsx")
        self.worksheet = self.workbook.add_worksheet()
        self.head = ['职位', '公司', '工资', '地点']
        self.worksheet.write_row('A1', self.head)
        self.list_1 = []

    def process_item(self, item, spider):
        row = 1
        col = 0
        list_1 = [item['job_name'], item['job_company'], item['job_money'], item['job_place']]
        self.list_1.append(list_1)
        for code in self.list_1:
            self.worksheet.write_row(row, col, code)
            row += 1
        return item

    def __del__(self):
        self.workbook.close()
